#include "srdatabase.h"

#include <io.h>

#include <QtCore>
#include <QDateTime>
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>

#include <openssl/md5.h>

SRDatabase SRDatabase::dbInstance_;
QString SRDatabase::strFilename_ = "auditview.db";

SRDatabase::SRDatabase(QObject *parent)
    : QObject(parent) {
}

SRDatabase::~SRDatabase() {
    if (sqlDatabase_.isOpen())
        sqlDatabase_.close();
}

QString SRDatabase::encryptionPswd(const QString &_pswd) {
    return _pswd;
    /// [1] MD5 第一次加密
    unsigned char ch_pswd[256] = {};
    char md5_pswd[256] = {};
    memcpy(ch_pswd,_pswd.toUtf8().data(),_pswd.toUtf8().length());
    MD5(ch_pswd, strlen((char*)ch_pswd),(unsigned char*)md5_pswd);

    /// [2] MD5("pswd"+MD5) 第二次加密
    std::string out_pswd("pswd:");
    out_pswd += md5_pswd;
    memset(md5_pswd,0,sizeof(md5_pswd));
    MD5((const unsigned char*)out_pswd.c_str(),out_pswd.length(),
        (unsigned char*)md5_pswd);

    /// [3] 整理返回值
    QString ret_pswd = QString::fromUtf8(md5_pswd);
#ifdef QT_DEBUG
    qDebug() << "pswd" << ret_pswd;
#endif
    return ret_pswd;
}

SRDatabase *SRDatabase::DB(void) {
    if (!SRDatabase::isExist()) {
#ifndef QT_DEBUG
        qFatal("error: please register db first.");
#endif
        return Q_NULLPTR;
    }

    return &dbInstance_;
}

int SRDatabase::open(QString _userName) {
    /// [1] 修改当前文件夹下的DB文件
    QString filename;
    DBPath(filename);

#ifdef QT_DEBUG
    qDebug() << "filename :" << filename;
#endif
    if (!isExist()) {
        return 2;
    }

    int ret = -1;
    do {
        if (QSqlDatabase::contains(strFilename_)) {
            sqlDatabase_ = QSqlDatabase::database(strFilename_);
        }
        else {
            sqlDatabase_ = QSqlDatabase::addDatabase("QSQLITE",strFilename_);
            sqlDatabase_.setDatabaseName(strFilename_);
        }
        if (!sqlDatabase_.open()) {
            /// @remark : 弹出一个提示框
            qWarning() << "Failed to connect database."
                       << sqlDatabase_.lastError();
            // 系统无法打开指定的设备或文件。
            ret = 110;
            break;
        }

        userName_ = _userName;
    } while(false);
    return ret;
}

int SRDatabase::close(void) {
    if (!sqlDatabase_.isOpen()) {
        return -1;
    }
    sqlDatabase_.close();
    return 0;
}

int SRDatabase::writeOperateLog(QString _doWhat, int _type)
{
    if (!sqlDatabase_.isOpen()) {
        return 1062;
    }

    QSqlQuery query(sqlDatabase_);
    QString sql = QString("INSERT INTO audit_log (name,log,type)"\
                          "VALUES ('%1', '%2', %3);")
                              .arg(userName_).arg(_doWhat).arg(_type);
#ifdef QT_DEBUG
    qDebug() << "write Operate Log :" << sql;
#endif
    if (!query.exec(sql)) {

        /// @remark : 弹出一个提示框
        qWarning() << "Failed to connect database."
                   << query.lastError();
    }
    return 0;
}

int SRDatabase::getOperateLog(int b, int e, QList<SRDatabase::stLogInfo> &_listLog)
{
    int ret = -1;
    do {
        if (!sqlDatabase_.isOpen()) {
#ifdef QT_DEBUG
        qDebug() << "data base is not open!";
#endif
            break;
        }

        QString sql = QString("SELECT id, log, name, crt_time FROM `audit_log` ORDER BY crt_time DESC LIMIT %1 OFFSET %2;")
                            .arg(abs(e-b)).arg(abs(b));

#ifdef QT_DEBUG
        qDebug() << "get operate log sql :" << sql;
#endif
        QSqlQuery query(sqlDatabase_);
        if (!query.exec(sql)) {
            qCritical() << "select operate log failed: " << query.lastError();
            ret = -1;
            break;
        }
        while(query.next()) {
            stLogInfo log_info;
            log_info.id = QString::number(query.value(0).toInt());
            log_info.info = query.value(1).toString();
            log_info.name = query.value(2).toString();
            log_info.time = query.value(3).toString();
            _listLog.push_back(log_info);
        }
        ret = 0;
    } while(false);

    return ret;
}

int SRDatabase::getOperateLogSize()
{
    int ret = -1;
    do {
        if (!sqlDatabase_.isOpen()) {
#ifdef QT_DEBUG
        qDebug() << "data base is not open!";
#endif
            break;
        }

        QString sql = QString("SELECT count(id) FROM `audit_log`");

#ifdef QT_DEBUG
        qDebug() << "get operate log size sql :" << sql;
#endif
        QSqlQuery query(sqlDatabase_);
        if (!query.exec(sql)) {
            qCritical() << "select operate log failed: " << query.lastError();
            ret = -1;
            break;
        }
        while(query.next()) {
            ret = query.value(0).toInt();
        }
    } while(false);

    return ret;
}

int SRDatabase::upXlsRecordExport(const QString &auditId, int isExport)
{

    int ret = -1;
    do {
        if (!sqlDatabase_.isOpen()) {
#ifdef QT_DEBUG
        qDebug() << "data base is not open!";
#endif
            break;
        }

        const QString& sql = QString("UPDATE `audit_record` SET is_export = %1 "\
                "WHERE audit_id = '%2';").arg(isExport).arg(auditId);

        QSqlQuery query(sqlDatabase_);
        if (!query.exec(sql)) {
            qCritical() << "UPDATE excel export status failed: " << query.lastError();
            ret = -1;
            break;
        }
        ret = 0;
    } while(false);

    return ret;


}
int SRDatabase::writeXlsRecord(
        const SRDatabase::stXlsRecord &_record,
        int _type) {

    Q_UNUSED(_type);

    int ret = -1;
    do {
        if (!sqlDatabase_.isOpen()) {
            qDebug() << "data base is not open!";
            break;
        }
        /// [1] 生成excel路径
        QString file_path = QString("storages/signfile");
        QDir dir(file_path);
        if(!dir.exists()) {
            dir.mkpath(file_path);
        }
        QString file_name = QString("%1/%2")
                .arg(file_path)
//                .arg(QDate::currentDate().toString("yyMMdd"))
                .arg(_record.auditId);


        if (QFile(file_name).exists()) {
            file_name += QTime::currentTime().toString("HHmmsszzz");

            qCritical() << QString("critical same file name insert, modify file name to %1.sig")
                           .arg(file_name);
        }

        file_name += ".sig";
#ifdef QT_DEBUG
            qDebug() << "sign file name : " << file_name;
#endif

        /// [2] 把文件写入本地
        QFile file(file_name);
        if (!file.open(QIODevice::Truncate
                  | QIODevice::WriteOnly)) {
            qWarning("write excel record failed,"\
                   "because sign file write failed.");
            return 4;
        }
        QTextStream in(&file);
        in << _record.sigDataHex << "\r\n" << _record.sigData;
        file.close();


        QString sql = QString("INSERT INTO audit_record (audit_id,sig_file,opt_name)" \
                              "VALUES ('%1', '%2', '%3');")
                            .arg(_record.auditId).arg(file_name).arg(userName_);

#ifdef QT_DEBUG
        qDebug() << "write excel record sql :" << sql;
#endif
        QSqlQuery query(sqlDatabase_);
        if (!query.exec(sql)) {
            qCritical() << "insert excel record failed: " << query.lastError();
            ret = -1;
            break;
        }
        ret = 0;

    } while(false);

    return ret;
}

int SRDatabase::getXlsTimeRecord(QString btime, QString etime, QList<SRDatabase::stXlsRecord> &_listXls)
{
    int ret = -1;
    do {
        if (!sqlDatabase_.isOpen()) {
    #ifdef QT_DEBUG
        qDebug() << "data base is not open!";
    #endif
            break;
        }

    QString sql = QString("SELECT id, audit_id, sig_file, opt_name, is_export, crt_time "\
                          "FROM `audit_record` WHERE crt_time > '%1'  AND crt_time < '%2';")
                        .arg(btime).arg(etime);

    #ifdef QT_DEBUG
        qDebug() << "get excel record sql :" << sql;
    #endif
        QSqlQuery query(sqlDatabase_);
        if (!query.exec(sql)) {
            qCritical() << "select excel record failed: " << query.lastError();
            ret = -1;
            break;
        }
        while(query.next()) {
            // 先解析文件
            QString temp_sign_file = query.value(2).toString();
            QFile temp_file(temp_sign_file);
            if (!temp_file.open(QIODevice::ReadOnly
                                | QIODevice::Text)) {
                qWarning() << QString("audit_id : %1,"\
                                      "read sign file failed,"\
                                      "the file name: %2")
                                        .arg(query.value(1).toString())
                                        .arg(temp_sign_file);
                ret = -2;

            }

            stXlsRecord xls_info;


            // sigData
            QTextStream txtInput(&temp_file);
            xls_info.sigDataHex = txtInput.readLine();
            xls_info.sigData = txtInput.readLine();
            temp_file.close();

            xls_info.id = QString::number(query.value(0).toInt());
            xls_info.auditId = query.value(1).toString();

            xls_info.name = query.value(3).toString();
            xls_info.isExport = query.value(4).toInt();
            xls_info.crtTime = query.value(5).toString();

            _listXls.push_back(xls_info);
        }
        ret = 0;
    } while(false);

    return ret;
}


int SRDatabase::getXlsRecord(int b, int e,
         QList<SRDatabase::stXlsRecord> &_listXls) {

    int ret = -1;
    do {
        if (!sqlDatabase_.isOpen()) {
#ifdef QT_DEBUG
        qDebug() << "data base is not open!";
#endif
            break;
        }

        QString sql = QString("SELECT id, audit_id, sig_file, opt_name, is_export, crt_time "\
                              "FROM `audit_record` ORDER BY crt_time DESC LIMIT %1 OFFSET %2;")
                            .arg(abs(e-b)).arg(abs(b));

#ifdef QT_DEBUG
        qDebug() << "get excel record sql :" << sql;
#endif
        QSqlQuery query(sqlDatabase_);
        if (!query.exec(sql)) {
            qCritical() << "select excel record failed: " << query.lastError();
            ret = -1;
            break;
        }
        while(query.next()) {
            // 先解析文件
            QString temp_sign_file = query.value(2).toString();
            QFile temp_file(temp_sign_file);
            if (!temp_file.open(QIODevice::ReadOnly
                                | QIODevice::Text)) {
                qWarning() << QString("audit_id : %1,"\
                                      "read sign file failed,"\
                                      "the file name: %2")
                                        .arg(query.value(1).toString())
                                        .arg(temp_sign_file);
                ret = -2;

            }

            stXlsRecord xls_info;


            // sigData
            QTextStream txtInput(&temp_file);
            xls_info.sigDataHex = txtInput.readLine();
            xls_info.sigData = txtInput.readLine();
            temp_file.close();

            xls_info.id = QString::number(query.value(0).toInt());
            xls_info.auditId = query.value(1).toString();

            xls_info.name = query.value(3).toString();
            xls_info.isExport = query.value(4).toInt();
            xls_info.crtTime = query.value(5).toString();

            _listXls.push_back(xls_info);
        }
        ret = 0;
    } while(false);

    return ret;
}

int SRDatabase::getXlsRecordSize()
{

    int ret = -1;
    do {
        if (!sqlDatabase_.isOpen()) {
#ifdef QT_DEBUG
        qDebug() << "data base is not open!";
#endif
            break;
        }

        QString sql = QString("SELECT count(id) FROM `audit_record`;");

#ifdef QT_DEBUG
        qDebug() << "get excel record sql :" << sql;
#endif
        QSqlQuery query(sqlDatabase_);
        if (!query.exec(sql)) {
            qCritical() << "select excel record failed: "
                        << query.lastError();
            ret = -1;
            break;
        }
        while(query.next()) {
            ret = query.value(0).toInt();
        }
    } while(false);

    return ret;
}

int SRDatabase::checkXlsRecord(const QString &_auditId)
{
        if (!sqlDatabase_.isOpen()) {
#ifdef QT_DEBUG
        qDebug() << "data base is not open!";
#endif
            return -1;
        }

        QString sql = QString("SELECT id "\
                              "FROM `audit_record`"\
                              "WHERE audit_id = '%1'")
                                .arg(_auditId);

#ifdef QT_DEBUG
        qDebug() << "get excel record sql :" << sql;
#endif
        QSqlQuery query(sqlDatabase_);
        if (!query.exec(sql)) {
            qCritical() << "select excel record failed: " << query.lastError();
            return -1;
        }
        while(query.next()) {
            return 0;
        }

    return 1;
}

bool SRDatabase::isExist(void) {
    QFile file(QString("%1/%2").arg(QDir::currentPath())
               .arg(strFilename_));
    if(file.exists()) {
        return true;
    }
    return false;
}
int SRDatabase::registerDB(void) {
    /// [1] 修改当前文件夹下的DB文件
    QString filename;
    DBPath(filename);

#ifdef QT_DEBUG
    qDebug() << "filename :" << filename;
#endif
    if (isExist()) {
        QFile::rename(filename, filename
               + QDateTime::currentDateTime().toString("yyyyMMddhhmmsszzz"));
    }

    /// [2] 检查当前注册的链接
    QSqlDatabase sql_database;
    if (QSqlDatabase::contains(strFilename_)) {
        sql_database = QSqlDatabase::database(strFilename_);
    }
    else {
        sql_database = QSqlDatabase::addDatabase("QSQLITE");
        sql_database.setDatabaseName(strFilename_);
    }

    if (!sql_database.open()) {
        /// @remark : 弹出一个提示框
        qWarning() << "Error: Failed to connect database." << sql_database.lastError();
        return 4;
    }

    /// [3] 开始注册数据库结构
    do {
        // 获得初始化的Sql语句
        QFile file(":/sql/auditview");
        QSqlQuery query(sql_database);
        if(!file.open(QIODevice::ReadOnly | QIODevice::Text)) {
            qFatal("Can't open DB file!");
            return 4;
        }
        while(!file.atEnd()) {
            QByteArray line = file.readLine();
            if (!query.exec(line)) {
#ifdef QT_DEBUG
                qWarning() << query.lastError();
#endif
                continue;
            }
        }
        file.close();

    } while(false);

    /// [4] 清理资源
    sql_database.removeDatabase(strFilename_);
    sql_database.close();
    return 0;
}

void SRDatabase::DBPath(QString &path)
{
    path = QDir::currentPath();
    path += "/";
    path += strFilename_;
}
